from itertools import groupby

from odoo import api,fields, models
from odoo.tools import DEFAULT_SERVER_DATETIME_FORMAT
from odoo.exceptions import UserError


class WorkorderScrapSumReport(models.Model):
    _name = 'workorder.scrap.sum.report'
    _description = "Workorder Scrap Sum Report"
    _auto = False

    production_id = fields.Many2one('mrp.production', string='Production')
    product_id = fields.Many2one('product.product', string='Product')

    discover_workcenter_id = fields.Many2one('mrp.workcenter', string='Discover Process')  # 发现工序
    discover_work_station_id = fields.Many2one('work.station', string='Discover Work Station')

    scrap_reason_id = fields.Many2one('scrap.reason', string="Scrap Reason")
    scrap_reason = fields.Char(related='scrap_reason_id.name', string="Scrap Reason")

    scrap_work_station_id = fields.Many2one('work.station', string='Work Station')
    work_station_code = fields.Char(related='scrap_work_station_id.code')
    work_station_name = fields.Char(related='scrap_work_station_id.name', string='Work Station')

    scrap_workcenter_id = fields.Many2one('mrp.workcenter', string='Responsibility Process')
    workcenter_name = fields.Char(related='scrap_workcenter_id.name', string='Responsibility Process')

    scrap_pcs_qty = fields.Float(string='Scrap Quantity')
    scrap_set_qty = fields.Float(string='Scrap Set Quantity')
    scrap_panel_qty = fields.Float(string='Scrap PNL Quantity', digits=(12,3))
    scrap_area = fields.Float(string='Scrap Area(SET)(㎡)', digits=(12,4))

    done_date = fields.Datetime('Date')

    @property
    def _table_query(self):
        start_date = self._context.get('start_date') or ''
        end_date = self._context.get('end_date') or ''
        product_id = self._context.get('product_id') or None
        where = 'where swsr.workorder_id is not null'
        if start_date and end_date:
            where += f"  and qa.s_confirm_date >= '{start_date}'  "
        if end_date:
            where += f" and qa.s_confirm_date <= '{end_date}'"
        if product_id:
            where += f" and mp.product_id = {product_id}"

        select_sql = f"""
            SELECT row_number() OVER() AS id,
                mp.id production_id,
                mw.product_id,
                mw.workcenter_id discover_workcenter_id,
                ws.id discover_work_station_id,
                swsr.scrap_reason_id,
                mwc.work_station_id as scrap_work_station_id,
                swsr.scrap_workcenter_id,
                swsr.scrap_qty AS scrap_pcs_qty,
                swsr.scrap_set_qty AS scrap_set_qty,
                swsr.scrap_qty / mpuv5.uom_value  AS scrap_panel_qty,
                qa.s_confirm_date done_date,
                CASE when ws.code in ('FQC', 'FQA', 'PKG') 
                     then round(swsr.scrap_qty * 1.0 / mp.s_value_set * mpuv.uom_value * mpuv2.uom_value / 1000000, 3)
                     else round(swsr.scrap_qty * 1.0 / mp.s_value_pnl * mpuv3.uom_value * mpuv4.uom_value / 1000000,3) end scrap_area
            FROM
                s_workorder_scrap_record swsr
                left join quality_alert qa on swsr.quality_alert_id = qa.id
                LEFT JOIN mrp_workorder mw ON mw.ID = swsr.workorder_id
                left join mrp_workcenter mwc2 on mw.workcenter_id = mwc2.id
                left join work_station ws on ws.id = mwc2.work_station_id
                LEFT JOIN mrp_workcenter mwc ON mwc.ID = swsr.scrap_workcenter_id 
                left join mrp_production mp on mw.production_id = mp.id
                left join mrp_production_uom_value mpuv on mpuv.production_id = mp.id and mpuv.uom_id in (select id from uom_uom where uom_uom.code = 'SETL')
                left join mrp_production_uom_value mpuv2 on mpuv2.production_id = mp.id and mpuv2.uom_id in (select id from uom_uom where uom_uom.code = 'SETW')
                left join mrp_production_uom_value mpuv3
                             on mpuv3.production_id = mp.id and mpuv3.uom_id in (select id from uom_uom where uom_uom.code = 'PNLW')
                left join mrp_production_uom_value mpuv4
                             on mpuv4.production_id = mp.id and mpuv4.uom_id in (select id from uom_uom where uom_uom.code = 'PNLL')
                left join mrp_production_uom_value mpuv5
                             on mpuv5.production_id = mp.id and mpuv5.uom_id in (select id from uom_uom where uom_uom.code = 'PANEL')
                left join mrp_production_uom_value mpuv6
                             on mpuv6.production_id = mp.id and mpuv6.uom_id in (select id from uom_uom where uom_uom.code = 'SET')
            {where}
            
            order by mp.id
        """
        return select_sql
